package com.pentofab.payrollmanagement.database.impl;

public interface AttendanceTableSQLConstants extends SQLConstants {

	String tableName = "attendance";
	// Primary key of the table
	String rowNumber = tableName + ".RowNumber";
	String employeeId = tableName + ".EmployeeId";
	String date = tableName + ".Date";
	String logIn = tableName + ".LogIn";
	String logOut = tableName + ".LogOut";
	String createBy = tableName + ".CreatedBy";
	String comments = tableName + ".Comments";
	String modifiedBy = tableName + ".ModifiedBy";
	String firstLogin = tableName + ".FirstLogin";
	String lastLogout = tableName + ".LastLogout";

	String inserIntoAttandance = new StringBuffer(insert_into).append(tableName)
			.append(openingBrace)
			.append(employeeId).append(comma)
			.append(date).append(comma)
			.append(logIn).append(comma)
			.append(logOut).append(comma)
			.append(createBy).append(comma)
			.append(modifiedBy).append(comma)
			.append(comments).append(comma)
			.append(rowNumber).append(comma)
			.append(firstLogin).append(comma)
			.append(lastLogout).append(closingBrace)
			.append(values).append(openingBrace)
			.append("?,?,?,?,?,?,?,?,?,?").append(closingBrace)
			.append(" ON DUPLICATE KEY UPDATE  attendance.LogIn = ? ,attendance.LogOut = ?").toString();

	String setLogOutTime = new StringBuffer(update).append(tableName)
			.append(set).append(logOut).append(equals).append(question)
			.append(where).append(employeeId).append(equals).append(question)
			.append(and).append(rowNumber).append(equals).append(question)
			.toString();

	
	
	String modifyAttandance = new StringBuffer(update).append(tableName).append(set)
			.append(logIn).append(equals).append(question).append(comma)
			.append(firstLogin).append(equals).append(question).append(comma)
			.append(logOut).append(equals).append(question).append(comma)
			.append(lastLogout).append(equals).append(question)
			.append(where)
			.append(employeeId).append(equals).append(question).append(and)
			.append(date).append(equals).append(question).append(and)
			.append(rowNumber).append(equals).append(question).toString();

	String retriveFromAttandance = new StringBuffer(select).append(rowNumber).append(comma)
			.append(firstLogin).append(comma).append(lastLogout)
			.append(comma).append(employeeId).append(comma).append(date)
			.append(comma).append(logIn).append(comma).append(logOut)
			.append(comma).append(comments).append(comma).append(createBy)
			.append(comma).append(modifiedBy).append(from).append(tableName)
			.append(space).append(where).append(employeeId).append(equals)
			.append(question).toString();

	//Will be appended to retriveFromAttandance when date is null
	String whenDateNull = new StringBuffer(and).append(str2Date).append(openingBrace).append(date).append(comma)
			.append(sqldateformat).append(closingBrace)
			.append(greaterthan).append(ifNull).append(openingBrace)
			.append(openingBrace).append(select).append(max)
			.append(openingBrace).append(str2Date).append(openingBrace).append(date).append(comma)
			.append(sqldateformat).append(closingBrace).append(closingBrace)
			.append(from).append(tableName).append(where).append(employeeId)
			.append(equals).append(question).append(and).append(lastLogout).append(equals)
			.append(question).append(closingBrace).append(comma).append(1).append(closingBrace).append(ORDEY_BY).append(rowNumber).toString();
	
	//Will be appended to retriveFromAttandance when date is not null
	String whenDateNotNull = new StringBuffer(and).append(date).append(equals).append(question).append(ORDEY_BY).append(rowNumber).toString();
	
	String updateLogOutStatus = new StringBuffer(update).append(tableName)
			.append(set).append(lastLogout).append(equals).append(question).append(where)
			.append(employeeId).append(equals).append(question).append(and).append(rowNumber)
			.append(equals).append(question).toString();
	
	String getMaxRowForUpdate = new StringBuffer(select).append(max)
			.append(openingBrace).append(rowNumber).append(closingBrace)
			.append(from).append(tableName).append(where).append(employeeId)
			.append(equals).append(question).toString();
	
	String getRowForGivenDate = new StringBuffer(and).append(date).append(equals).append(question).toString();
	
	
	String deleteAttEtry = new StringBuffer(delete).append(from).append(tableName)
			.append(where).append(employeeId).append(equals).append(question).append(and)
			.append(rowNumber).append(equals).append(question).append(and)
			.append(date).append(equals).append(question).toString();
	
	
	
	String getEmployeeDailyConsolidatedTimesheetInfo = "SELECT * FROM (SELECT att.employeeid AS eid,att.RowNumber as rowNumber,emp.unitCode as unit,emp.weeklyoff,emp.hoursperday,emp.name AS NAME,att.date AS attDate,MIN(att.login) AS from_time"
			+ ",MAX(att.logout) AS to_time,SEC_TO_TIME(SUM((  CASE WHEN TIME_TO_SEC(att.logout) < TIME_TO_SEC(att.login) THEN (TIME_TO_SEC(att.logout)+ (24*3600)) ELSE  TIME_TO_SEC(att.logout) END )-TIME_TO_SEC(att.login )))AS total,"
			+ "emp.CompanyCode AS company,emp.DeptCode AS department,att.comments as reason,att.FirstLogin as firstLogin, LastLogout as lastLogOut FROM attendance att JOIN employee emp ON att.EmployeeId = emp.employeeid "
			+ " AND STR_TO_DATE(att.Date,'%d-%m-%Y') >= STR_TO_DATE(?, '%d-%m-%Y') AND "
			+ "STR_TO_DATE(att.Date,'%d-%m-%Y')  <= STR_TO_DATE(?, '%d-%m-%Y') "
			+ "GROUP BY"
			+ " att.date,att.employeeid"
			+ " UNION"
			+ " SELECT emp.employeeid AS eid,0 AS rowNumber,'','leave','hrs',emp.name AS NAME,"
			+ "'LEAVE' AS attDate, STR_TO_DATE(lev.from_date,'%d-%m-%Y') AS loginFrom, STR_TO_DATE(lev.to_date,'%d-%m-%Y') AS LoginTo, lev.No_of_days, "
			+ "emp.CompanyCode AS company,emp.DeptCode AS department,lev.reason as reason,0,0 FROM employee emp JOIN  leave_system lev ON  lev.eId = emp.employeeid  "
			+ "AND STR_TO_DATE(lev.from_date,'%d-%m-%Y') >= STR_TO_DATE(?, '%d-%m-%Y')  "
			+ "AND STR_TO_DATE(lev.To_date,'%d-%m-%Y') <= STR_TO_DATE(?, '%d-%m-%Y')) DATA WHERE 1=1 ";
	
	String getEmployeeTimeSheetInfo = "SELECT * FROM (SELECT att.employeeid AS eid,att.RowNumber AS rowNumber,emp.unitCode as unit,emp.weeklyoff,emp.hoursperday,emp.name AS NAME,att.date AS attDate,att.login AS from_time,att.logout AS to_time,"
			+ " SEC_TO_TIME(SUM((  CASE WHEN TIME_TO_SEC(att.logout) < TIME_TO_SEC(att.login) THEN (TIME_TO_SEC(att.logout)+ (24*3600)) ELSE  TIME_TO_SEC(att.logout) END )-TIME_TO_SEC(att.login )))AS total,"
			+ " emp.CompanyCode AS company,emp.DeptCode AS department,att.comments AS reason,att.FirstLogin as firstLogin, LastLogout as lastLogOut FROM attendance att "
			+ " JOIN employee emp ON att.EmployeeId = emp.employeeid  AND "
			+ " STR_TO_DATE(att.Date,'%d-%m-%Y') >= STR_TO_DATE(?, '%d-%m-%Y') AND "
			+ " STR_TO_DATE(att.Date,'%d-%m-%Y')  <= STR_TO_DATE(?, '%d-%m-%Y') "
			+ " GROUP BY att.date,att.employeeid,att.RowNumber "
			+ " UNION "
			+ " SELECT emp.employeeid AS eid,0,'','leave','hrs',emp.name AS NAME,'LEAVE' AS attDate,"
			+ "STR_TO_DATE(lev.from_date,'%d-%m-%Y') AS loginFrom, STR_TO_DATE(lev.to_date,'%d-%m-%Y') AS LoginTo, lev.No_of_days, emp.CompanyCode AS company,emp.DeptCode AS department,"
			+ " lev.reason AS reason,1,1 FROM employee emp JOIN  leave_system lev ON  lev.eId = emp.employeeid  "
			+ "  AND STR_TO_DATE(lev.from_date,'%d-%m-%Y') >= STR_TO_DATE(?, '%d-%m-%Y')  "
			+ " AND STR_TO_DATE(lev.To_date,'%d-%m-%Y') <= STR_TO_DATE(?, '%d-%m-%Y')) DATA WHERE 1=1 ";

	String getEmployeeMonthlyConsolidatedTimesheetInfo = "SELECT * FROM (SELECT att.employeeid AS eid,att.RowNumber as rowNumber,emp.unitCode as unit,emp.weeklyoff,emp.hoursperday,emp.name AS NAME,att.date AS attDate,MIN(att.login) AS from_time"
			+ ", MAX(att.logout) AS to_time,SEC_TO_TIME(SUM((  CASE WHEN TIME_TO_SEC(att.logout) < TIME_TO_SEC(att.login) THEN (TIME_TO_SEC(att.logout)+ (24*3600)) ELSE  TIME_TO_SEC(att.logout) END )-TIME_TO_SEC(att.login )))AS total,"
			+ " emp.CompanyCode AS company,emp.DeptCode AS department,att.comments as reason,att.FirstLogin as firstLogin, LastLogout as lastLogOut FROM attendance att JOIN employee emp ON att.EmployeeId = emp.employeeid "
			+ " AND STR_TO_DATE(att.Date,'%d-%m-%Y') >= STR_TO_DATE(?, '%d-%m-%Y') AND "
			+ " STR_TO_DATE(att.Date,'%d-%m-%Y')  <= STR_TO_DATE(?, '%d-%m-%Y') "
			+ " GROUP BY"
			+ " att.employeeid"
			+ " UNION"
			+ " SELECT emp.employeeid AS eid,0 AS rowNumber,'','leave','hrs',emp.name AS NAME,"
			+ "'LEAVE' AS attDate, STR_TO_DATE(lev.from_date,'%d-%m-%Y') AS loginFrom, STR_TO_DATE(lev.to_date,'%d-%m-%Y') AS LoginTo, lev.No_of_days, "
			+ " emp.CompanyCode AS company,emp.DeptCode AS department,lev.reason as reason,0,0 FROM employee emp JOIN  leave_system lev ON  lev.eId = emp.employeeid  "
			+ " AND STR_TO_DATE(lev.from_date,'%d-%m-%Y') >= STR_TO_DATE(?, '%d-%m-%Y')  "
			+ " AND STR_TO_DATE(lev.To_date,'%d-%m-%Y') <= STR_TO_DATE(?, '%d-%m-%Y')) DATA WHERE 1=1 ";
	
	String updaeRowNUm = "update attendance set RowNumber = ? where employeeid =? and login = ? and logout = ? and date = ?";
	
	String getAttENtry = "SELECT emp.EmployeeId AS empid,emp.name AS NAME ,att.LogIn AS LOGIN,att.LogOut AS LogOut,att.RowNumber AS RowNum"
			+ " FROM employee emp LEFT JOIN attendance att  ON (emp.EmployeeId = att.EmployeeId AND att.date = ?) "
			+ " WHERE emp.deletionDate ='' AND emp.CompanyCode = ? and emp.EmployeeId not in (select leave_system.EID from leave_system where "
			+ " DATE_FORMAT(STR_TO_DATE(leave_system.From_Date,  '%e-%c-%Y'),'%d-%m-%Y')<=DATE_FORMAT(STR_TO_DATE( ? ,  '%e-%c-%Y'),'%d-%m-%Y') "
			+ " and DATE_FORMAT(STR_TO_DATE(leave_system.To_Date,  '%e-%c-%Y'),'%d-%m-%Y')>=DATE_FORMAT(STR_TO_DATE( ? ,  '%e-%c-%Y'),'%d-%m-%Y') "
			+ " and 1=( CASE  WHEN leave_system.halfday='11' and (leave_system.From_Date= ?  OR leave_system.To_Date= ? ) then 0 ELSE 1 END ) "
			+ " and 1=( CASE  WHEN leave_system.halfday='10' and (leave_system.From_Date= ? ) then 0 ELSE 1 END ) "
			+ " and 1=( CASE  WHEN leave_system.halfday='01' and (leave_system.To_Date= ? ) then 0 ELSE 1 END ))" ;
}
